package org.jeecg.modules.online.graphreport.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.cgform.util.OnlineDbHandler;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GraphreportUtil extends CgformUtil {

	public static final String SQL_PARENT_SELECT_BEGIN = "SELECT * FROM (";
	public static final String SQL_PARENT_SELECT_END = ") temp_table WHERE 1=1";

	/**
	 * 将JSON字符串转为Map查询条件
	 * 
	 * @param paramString
	 *            JSON查询条件
	 * @return Map&lt;String, Objecg&gt;
	 */
	public static List<Map<String, Object>> getParameterMap(String paramString) {
		// json对象转Map
		List<Map<String, Object>> returnList = new ArrayList<Map<String, Object>>();

		String params;
		try {
			params = URLDecoder.decode(paramString, "UTF-8");
		} catch (Exception e) {
			return null;
		}

		JSONArray array = JSONArray.parseArray(params);
		if (array == null) {
			return null;
		}

		for (int i = 0; i < array.size(); i++) {
			JSONObject object = array.getJSONObject(i);
			returnList.add((Map<String, Object>) object);
		}

		return returnList;
	}

	/**
	 * 拼装条件
	 * 
	 * @param sql
	 * @param paramString
	 *            前端传过来的查询条件 JSON 字符串
	 * @param paramsMap
	 *            用户自定义的查询条件，SQL里没有定义的且value不为空的
	 * @return SELECT * FROM ( sql ) AS `temporary` <br>
	 *         WHERE 1=1 and A1=1 and A2=2 ... and An=n
	 */
	public static String getSplicingConditionSql(String sql, String paramString, Map<String, Object> paramsMap) {
		List<Map<String, Object>> params = getParameterMap(paramString);
		if (params == null) {
			params = new ArrayList<>();
		}
		// 拼接用户自定义的查询条件
		for (String key : paramsMap.keySet()) {
			Map<String, Object> map = new HashMap<>();
			map.put("fieldName", key);
			map.put("value", paramsMap.get(key));
			map.put("fieldType", "string");
			map.put("searchMode", QUERY_MODE_SINGLE);
			params.add(map);
		}
		sql =  getSplicingConditionSql(sql, params);
		// 替换系统内置变量
		sql = QueryGenerator.convertSystemVariables(sql);
		return sql;
	}

	/**
	 * 获取拼接后的条件SQL
	 * 
	 * @param sql
	 * @param params
	 *            前端传过来的查询条件
	 * @return SELECT * FROM ( sql ) AS `temporary` <br>
	 *         WHERE 1=1 and A1=1 and A2=2 ... and An=n
	 */
	public static String getSplicingConditionSql(String sql, List<Map<String, Object>> params) {
		if (params == null) {
			return sql;
		}

		StringBuffer sb = new StringBuffer();

		sb.append(SQL_PARENT_SELECT_BEGIN);
		// 去掉最后一个分号
		if (sql.trim().lastIndexOf(";") == sql.length() - 1) {
			sql = sql.substring(0, sql.length() - 1);
		}
		sb.append(sql);
		sb.append(SQL_PARENT_SELECT_END);

		try {
			for (Map<String, Object> map : params) {
				String value = map.get("value").toString();
				String fieldName = map.get("fieldName").toString();
				String fieldType = map.get("fieldType").toString();
				String searchMode = map.get("searchMode").toString();

				// 判断数据库类型
				// String dataBase = DbTableUtil.getDatabaseType();
				// if (DbTableUtil.DB_TYPE_ORACLE.equals(dataBase)) {
				// sb.append("to_date(#{" + CREATE_TIME + "},'yyyy-MM-dd
				// HH24:mi:ss')");
				// } else {
				// sb.append("#{" + CREATE_TIME + "}");
				// }
				
				// 判断是否是单条件查询
				if (QUERY_MODE_SINGLE.equalsIgnoreCase(searchMode)) {
					if (!StringUtils.isEmpty(value)) {
						boolean isString = !OnlineDbHandler.isNumField(fieldType);
						String fieldCondition = QueryGenerator.getSingleQueryConditionSql(fieldName.toLowerCase(), "", value, isString);
						sb.append(SQL_AND + fieldCondition);
					}
				} else {
					// 范围查询
					List<Object> values = (List<Object>) JSONArray.parseArray(value);
					Object _begin = values.get(0);
					Object _end = values.get(1);

					if (_begin != null) {
						sb.append(SQL_AND + fieldName + SQL_GE);
						if (OnlineDbHandler.isNumField(fieldType)) {
							// 如果数字类型直接拼在SQL后面
							sb.append(_begin.toString());
						} else {
							// 如果字符串类型需要加单引号
							sb.append(SQL_SQ + _begin.toString() + SQL_SQ);
						}
					}
					if (_end != null) {
						sb.append(SQL_AND + fieldName + SQL_LE);
						if (OnlineDbHandler.isNumField(fieldType)) {
							sb.append(_end.toString());
						} else {
							sb.append(SQL_SQ + _end.toString() + SQL_SQ);
						}
					}
				}
			}
			return sb.toString();
		} catch (Exception e) {
			return sql;
		}

	}

	/**
	 * 字典值替换文本通用方法
	 *
	 * @param dictOptions 字典数组
	 * @param text        字典值
	 * @return String
	 */
	public static String filterDictText(List<DictModel> dictOptions, String text) {
		if (text != null && dictOptions != null && dictOptions.size() > 0) {
			List<String> result = new ArrayList<>();
			// 允许多个逗号分隔，允许传数组对象
			String[] splitText = text.trim().split(",");

			for (String txt : splitText) {
				String dictText = txt;
				for (DictModel dictItem : dictOptions) {
					if (dictItem.getValue().equals(txt)) {
						dictText = dictItem.getText();
						break;
					}
				}
				result.add(dictText);
			}
			return String.join(",", result);
		}
		return text;
	}

}
